<?php

namespace app\admin\controller\report;

use app\common\controller\Backend;
use think\Db;
use Exception;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\Reader\Csv;
use app\admin\model\AuthGroup;

/**
 * 报表管理
 *
 * @icon fa fa-user
 */
class Details extends Backend
{

    protected $searchFields = 'summary.reportname';
	protected $relationSearch = true;
	
    /**
     * @var \app\admin\model\Report
     */
    protected $model = null;

    public function _initialize()
    {
        parent::_initialize();
        $this->model = model('ReportDetails');
		$this->reportModel = model('Report');
    }
	
	/**
     * 明细
     */
    public function index()
    {
        //设置过滤方法
        $this->request->filter(['strip_tags', 'trim']);
        if ($this->request->isAjax()) {
            //如果发送的来源是Selectpage，则转发到Selectpage
            if ($this->request->request('keyField')) {
                return $this->selectpage();
            }

			$filter = json_decode($this->request->get("filter"),true);
			if(isset($filter['summary.reporttime'])){
				$year = $filter['summary.reporttime'];
				$filter['summary.reporttime'] = strtotime($filter['summary.reporttime'].'-1-1');
				$this->request->get(["filter"=>json_encode($filter)]);
			}else{
				$year = date('Y');
			}

            list($where, $sort, $order, $offset, $limit) = $this->buildparams();
			
			$map = [];
			$groupIds = $this->auth->getGroupIds();
			$groupPid = AuthGroup::where('id', $groupIds[0])->value('pid');
			if($groupPid != 0 && $groupPid != 1)
			{
				$map['report_details.depart_id'] = $this->auth->depart_id;
			}
			
            $list = $this->model
				->with(['summary','depart'])
                ->where($where)
				->where($map)
                ->order($sort, $order)
                ->paginate($limit);

            $result = array("total" => $list->total(), "rows" => $list->items(), "year" => $year);
			
            return json($result);
        }
		
		$this->assignconfig('year',date('Y'));
        return $this->view->fetch();
    }
	
	/**
     * 导入
     */
    public function imports($ids = null)
    {
		$row = $this->model->get($ids);
        if (!$row) {
            $this->error(__('No Results were found'));
        }
		
		if (false === $this->request->isPost()) {
			$this->view->assign('row', $row);
            return $this->view->fetch();
        }
		
		$this->token();
        $params = $this->request->post('row/a');
        if (empty($params)) {
            $this->error(__('Parameter %s can not be empty', ''));
        }
		
		if($row['attachment'])
		{
			$this->error(__('已经上传过了，如果要重新上传，请先删除'));
		}

        $result = false;
        Db::startTrans();
        try {
            $result = $row->allowField(true)->save($params);
			$this->importData($ids, $params['attachment']);
			
			$unfinished = $this->model->where('summary_id', $row['summary_id'])->where('status', 0)->find();
			if(!$unfinished)
			{
				model('ReportSummary')->where('id', $row['summary_id'])->update(['status'=>1]);
			}
			
            Db::commit();
        } catch (Exception $e) {
            Db::rollback();
            $this->error($e->getMessage());
        }
        if ($result === false) {
            $this->error(__('No rows were inserted'));
        }
        $this->success('上传成功', 'index');
    }
	
	private function importData($ids, $file)
	{
		$row = $this->model->get($ids);
        if (!$row) {
            $this->error(__('No Results were found'));
        }

        if (!$file) {
            $this->error(__('Parameter %s can not be empty', 'file'));
        }
        $filePath = ROOT_PATH . DS . 'public' . DS . $file;
        if (!is_file($filePath)) {
            $this->error(__('No results were found'));
        }
        //实例化reader
        $ext = pathinfo($filePath, PATHINFO_EXTENSION);
        if (!in_array($ext, ['csv', 'xls', 'xlsx'])) {
            $this->error(__('Unknown data format'));
        }
        if ($ext === 'csv') {
            $file = fopen($filePath, 'r');
            $filePath = tempnam(sys_get_temp_dir(), 'import_csv');
            $fp = fopen($filePath, 'w');
            $n = 0;
            while ($line = fgets($file)) {
                $line = rtrim($line, "\n\r\0");
                $encoding = mb_detect_encoding($line, ['utf-8', 'gbk', 'latin1', 'big5']);
                if ($encoding !== 'utf-8') {
                    $line = mb_convert_encoding($line, 'utf-8', $encoding);
                }
				
                if ($n == 0 || preg_match('/^".*"$/', $line)) {
                    fwrite($fp, $line . "\n");
                } else {
                    fwrite($fp, "'" . str_replace(["'", ","], ["''", "','"], $line) . "'\n");
                }
                $n++;
            }
            fclose($file) || fclose($fp);

            $reader = new Csv();
        } elseif ($ext === 'xls') {
            $reader = new Xls();
        } else {
            $reader = new Xlsx();
        }

        try{
            if (!$PHPExcel = $reader->load($filePath)) {
                $this->error(__('Unknown data format'));
            }
            $currentSheet = $PHPExcel->getSheet(0);  //读取文件中的第一个工作表
            $allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列号
            $allRow = $currentSheet->getHighestRow(); //取得一共有多少行
			
			$adjustBudgetAmount = 0;
			$executeAmount = 0;
			$allAdjustBudgetAmount = 0;
			$allExecuteAmount = 0;
			for ($currentRow = 6; $currentRow < $allRow; $currentRow++) {
				$category = $currentSheet->getCellByColumnAndRow(4, $currentRow)->getValue();
				if($category)
				{
					$category = trim($category);
					$value1 = $currentSheet->getCellByColumnAndRow(28, $currentRow)->getValue();
					$value2 = $currentSheet->getCellByColumnAndRow(30, $currentRow)->getValue();
					if(preg_match("/^208/", $category) || preg_match("/^210/", $category) 
						|| preg_match("/^221/", $category) || preg_match("/^2110101/", $category) 
						|| preg_match("/^2110199/", $category) || preg_match("/^2111102/", $category))
					{
						
						$adjustBudgetAmount += floatval($value1);
						$executeAmount += floatval($value2);
					}
					$allAdjustBudgetAmount += floatval($value1);
					$allExecuteAmount += floatval($value2);
				}
			}
			
			$row->save(['adjustbudgetamount'=>floatval($adjustBudgetAmount), 'executeamount'=>floatval($executeAmount), 'alladjustbudgetamount'=>floatval($allAdjustBudgetAmount), 'allexecuteamount'=>floatval($allExecuteAmount), 'status'=>1]);
			
		} catch (Exception $exception) {
            $this->error($exception->getMessage());
        }
	}

    /**
     * 删除
     */
    public function del($ids = "")
    {
        if (!$this->request->isPost()) {
            $this->error(__("Invalid parameters"));
        }
        $ids = $ids ? $ids : $this->request->post("ids");
        if (!$ids) {
            $this->error(__('Parameter %s can not be empty', 'ids'));
        }
		
		$summary_ids = $this->model->where('id', 'in', $ids)->column('summary_id');
        $this->model->where('id', 'in', $ids)->update(['attachment'=>'', 'adjustbudgetamount'=>0, 'executeamount'=>0, 'alladjustbudgetamount'=>0, 'allexecuteamount'=>0, 'status'=>0]);
		model('ReportSummary')->where('id', 'in', $summary_ids)->update(['status'=>0]);

		$this->success();
    }
}
